# Title     : TODO
# Objective : TODO
# Created by: Administrator
# Created on: 2021/10/18
# 安装RMySQL
install.packages('RMySQL')
library(RMySQL)
# 建立连接 获取数据
conn <- dbConnect(MySQL(), dbname = 'spark', username = 'root', password = 'root', host = '127.0.0.1', port = 3306)
raw_user <- dbGetQuery(conn, 'select * from raw_user')
# # 绘制用户行为柱状图
library(ggplot2)
# ggplot(raw_user, aes(behavior_type)) + geom_histogram()
# # 销量前十的商品种类
# temp = subset(raw_user, behavior_type == 4)
# count <- sort(table(temp$item_category), decreasing = T)
# print(count[1:10])
# result <- as.data.frame(count[1:10])
# ggplot(result, aes(Var1, Freq, col = factor(Var1))) + geom_point()
# # 月份消费图
# ggplot(raw_user, aes(behavior_type, col = factor(month))) +
#   geom_histogram() +
#   facet_grid(. ~ month)

#
summary(raw_user)
# 时间浏览图
hourLive <- table(raw_user$hour)
df <- as.data.frame(hourLive)
ggplot(df, aes(Var1, Freq)) +
  geom_point(col = 'red') +
  labs(x = 'Hour', y = 'Person') +
  geom_line(aes(group = ""), col = 'green')
# PV
library(dplyr)
library(readr)
library(dtplyr)
df <- as.data.frame(raw_user)
daily <- df %>% lazy_dt %>% na.omit(day)
hourly <- df %>% lazy_dt %>% na.omit(hour)
pv.daily <- daily %>%
  group_by(day) %>%
  summarise(pv = n()) %>%
  as_tibble()
p1 <- ggplot(pv.daily, aes(day, pv)) +
  geom_step(size = 1) +
  ylim(c(20000, 50000)) +
  theme_bw() +
  labs(x = "")
buy.daily <- daily %>%
  filter(behavior_type == 4) %>%
  group_by(user_id) %>%
  summarise(n = n()) %>%
  as_tibble()
ggplot(buy.daily, aes(n)) +
  geom_histogram(stat = "count") +
  theme_bw() +
  labs(x = "")

daily.totle <- daily %>%
  filter(behavior_type == 4) %>%
  group_by(day) %>%
  summarise(totle = n()) %>%
  as_tibble()
daily.count <- daily %>%
  filter(behavior_type == 4) %>%
  group_by(day) %>%
  distinct(user_id) %>%
  summarise(count = n()) %>%
  as_tibble()
full_join(daily.totle, daily.count) %>%
  mutate(freq = totle / count) %>%
  ggplot(aes(day, freq)) +
  geom_line(size = 1) +
  theme_bw() +
  labs(x = "", y = "")